SQL Server on Linux by Jasmin Azemovic

Author:Jasmin Azemovic [Azemovic, Jasmin]
Language: eng
Format: azw3
Tags: COM005000 - COMPUTERS / Enterprise Applications / General, COM021050 - COMPUTERS / Databases / Servers, COM018000 - COMPUTERS / Data Processing
Publisher: Packt Publishing
Published: 2017-08-14T04:00:00+00:00

Multiple joins

Generating detailed information about an entity in a single result set will often require data from multiple tables. This means you will have to apply a corresponding type of JOIN on more than two tables.

The next query will collect information on the quantity of every product on each production location. The required data are located in three different tables: Product (contains data about products), ProductInventory (contains data about the quantity of every product on each location), and Location (contains data about the location of production). It is important to note which common columns or keys are used to join tables:

1> SELECT TOP 10 P.Name AS Product, L.Name AS Location, I.Quantity 2> FROM Production.Product AS P 3> INNER JOIN Production.ProductInventory AS I 4> ON P.ProductID = I.ProductID 5> INNER JOIN Production.Location L 6> ON L.LocationID = I.LocationID 7> ORDER BY Quantity DESC 8> GO Product Location Quantity ----------------------- ----------------------- -------- Seat Lug Miscellaneous Storage 924 Hex Nut 7 Miscellaneous Storage 897 Spokes Miscellaneous Storage 888 Hex Nut 14 Miscellaneous Storage 780 Hex Nut 19 Subassembly 763 Seat Lug Final Assembly 729 Touring Rim Subassembly 724 Seat Stays Miscellaneous Storage 715 Hex Nut 10 Miscellaneous Storage 710 Spokes Subassembly 702 (10 rows affected)

If you want to narrow the search to a specific location and quantity, it is necessary to define the search conditions in the WHERE clause. For example:

WHERE L.Name = 'Tool Crib' AND I.Quantity < 200

As a special form of JOIN we can consider self-join, which refers to any kind of join used to join a table to itself. Self-join is used in cases where the inner and outer queries refer to the same table. The following query example will return only those products with a price that is higher than the average prices of the same sub-category:

1> SELECT TOP 4 P1.Name, P1.ListPrice 2> FROM Production.Product AS P1 3> INNER JOIN Production.Product AS P2 4> ON P1.ProductSubcategoryID = P2.ProductSubcategoryID 5> GROUP BY P1.Name, P1.ListPrice 6> HAVING P1.ListPrice > AVG (P2.ListPrice) 7> GO Name ListPrice -------------------------------------- ---------- Mountain-100 Silver, 38 3399.9900 Mountain-100 Silver, 42 3399.9900 Mountain-100 Silver, 44 3399.9900 Mountain-100 Silver, 48 3399.9900 (5 rows affected)

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.